import React,{Component} from 'react'
import SQLiteStorage from 'react-native-sqlite-storage';

SQLiteStorage.DEBUG(true);
var database_name = "water.db";
var database_version = "2.0";
var database_displayname = "MySQLite";
var database_size = -1;
var db;
const CostMaster_TABLE_NAME = "cost_master";
const History_TABLE_NAME = "cost_master_his";//历史表
const MeterBooks_TABLE_NAME = "meter_books";
export default class SQLite extends Component{

    render(){
        return null;
    }
	
    componentWillUnmount(){
        if(db){
            this._successCB('close');
            db.close();
        }else {
            console.log("SQLiteStorage not open");
        }
    }
	
    open(){
        db = SQLiteStorage.openDatabase(
            database_name,
            database_version,
            database_displayname,
            database_size,
            ()=>{
                this._successCB('open');
            },
            (err)=>{
                this._errorCB('open',err);
            });
    }
	
    createTable(){
        this.createCostMasterTable();
		this.createCostMasterHisTable();
		this.createMeterBooksTable();
    }
	
	createCostMasterTable(){
		if (!db) {
            this.open();
        }
        //创建表
        db.transaction((tx)=> {
			var createSql='CREATE TABLE IF NOT EXISTS ' + CostMaster_TABLE_NAME + '(' +
                'sno VARCHAR PRIMARY KEY NOT NULL,' +
                'cust_code VARCHAR,' +
                'cust_name VARCHAR,' +
                'meter_code VARCHAR,' +
				'bookId INTEGER,' +
                'wnid VARCHAR,' +
				'create_time VARCHAR,' +
				'prior_reading_time datetime,' +
				'prior_reading DECIMAL(10,2),' +
				'curr_reading_time datetime,' +
				'curr_reading DECIMAL(10,2),' +
				'original DECIMAL(10,2),' +
				'quantity DECIMAL(10,2),' +
				'water_cost DECIMAL(10,2),' +
				'other_cost DECIMAL(10,2),' +
				'sewage_cost DECIMAL(10,2),' +
				'build_cost DECIMAL(10,2),' +
				'late_fee DECIMAL(10,2),' +
				'pay_date VARCHAR,' +
				'bill_flag INTEGER,' +
				'status_id VARCHAR,' +
				'pre_status INTEGER,' +
				'curr_month VARCHAR,' +
				'operator VARCHAR,' +
				'reader VARCHAR,' +
				'memo VARCHAR,' +
                'postpone INTEGER'
                + ');'
            tx.executeSql(createSql, [], ()=> {
                    this._successCB('executeSql');
                }, (err)=> {
                    this._errorCB('executeSql', err);
            	}
			);
		}, (err)=> {
            this._errorCB('transaction', err);
        }, ()=> {
            this._successCB('transaction');
        })
	}
	
	createCostMasterHisTable(){
		if (!db) {
            this.open();
        }
        //创建表
        db.transaction((tx)=> {
			tx.executeSql('CREATE TABLE IF NOT EXISTS ' + History_TABLE_NAME + '(' +
                'sno VARCHAR PRIMARY KEY NOT NULL,' +
                'cust_code VARCHAR,' +
                'cust_name VARCHAR,' +
                'meter_code VARCHAR,' +
				'bookId INTEGER,' +
                'wnid VARCHAR,' +
				'create_time DATETIME,' +
				'prior_reading_time VARCHAR,' +
				'prior_reading DECIMAL(10,2),' +
				'curr_reading_time VARCHAR,' +
				'curr_reading DECIMAL(10,2),' +
				'original DECIMAL(10,2),' +
				'quantity DECIMAL(10,2),' +
				'water_cost DECIMAL(10,2),' +
				'other_cost DECIMAL(10,2),' +
				'sewage_cost DECIMAL(10,2),' +
				'build_cost DECIMAL(10,2),' +
				'late_fee DECIMAL(10,2),' +
				'pay_date VARCHAR,' +
				'bill_flag INTEGER,' +
				'status_id VARCHAR,' +
				'pre_status INTEGER,' +
				'curr_month VARCHAR,' +
				'operator VARCHAR,' +
				'reader VARCHAR,' +
				'memo VARCHAR,' +
                'postpone INTEGER'
                + ');'
                , [], ()=> {
                    this._successCB('executeSql');
                }, (err)=> {
                    this._errorCB('executeSql', err);
                }
			);
		}, (err)=> {
            this._errorCB('transaction', err);
        }, ()=> {
            this._successCB('transaction');
        })
	}
	
	createMeterBooksTable(){
		if (!db) {
            this.open();
        }
        //创建表
        db.transaction((tx)=> {
			tx.executeSql('CREATE TABLE IF NOT EXISTS ' + MeterBooks_TABLE_NAME + '(' +
				'bookId INTEGER PRIMARY KEY NOT NULL,' +
				'bookCode VARCHAR,' +
                'bookName VARCHAR,' +
				'status INTEGER,' +
				'anchor INTEGER,' +
				'del INTEGER' 
                + ');'
                , [], ()=> {
                    this._successCB('executeSql');
                }, (err)=> {
                    this._errorCB('executeSql', err);
                }
			);
		}, (err)=> {
            this._errorCB('transaction', err);
        }, ()=> {
            this._successCB('transaction');
        })
	}
	
	//保存cost_master记录
    saveCostMaster(costMaster){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql(
                    'INSERT INTO '+CostMaster_TABLE_NAME+' (sno,bookId,cust_code,meter_code,cust_name,prior_reading_time,curr_reading_time,prior_reading,curr_reading,quantity,curr_month,wnid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)',
                    [costMaster.getSno(),costMaster.getBookId(),costMaster.getCustCode(),costMaster.getMeterCode(),costMaster.getCustName(),costMaster.getPriorReadingTime(),costMaster.getCurrReadingTime(),costMaster.getPriorReading(),costMaster.getCurrReading(),costMaster.getQuantity(),costMaster.getCurrMonth(),costMaster.getWnid()],
                    ()=>{
                        this._successCB('saveCostMaster');
                        resolve();
                    },
                    (err)=>{
                        this._errorCB('saveCostMaster',err);
                        reject();
                    })
            }else {
                reject('db not open');
            }
        });
    }
	
	insertMeterBooks(meterBooks){//保存抄表册记录
        return new Promise((resolve, reject)=>{
            if(db){
				db.executeSql('INSERT INTO '+MeterBooks_TABLE_NAME+' (bookId,bookCode,bookName,status,anchor,del) VALUES(?,?,?,?,?,?)',
					[meterBooks.getBookId(),meterBooks.getBookCode(),meterBooks.getBookName(),meterBooks.getStatus(),meterBooks.getAnchor(),meterBooks.getDel()],
					()=>{
						this._successCB('save '+MeterBooks_TABLE_NAME);
						resolve();
					},
					(err)=>{
						this._errorCB('save '+MeterBooks_TABLE_NAME,err);
						reject();
					}
				)
            }else {
                reject('db not open');
            }
        });

    }
	
	updateMeterBooks(meterBooks){//保存抄表册记录
		return new Promise((resolve, reject)=>{
            if(db) {
				let sql="UPDATE " + MeterBooks_TABLE_NAME + " SET ";
				if(undefined!==meterBooks.getBookCode()&&meterBooks.getBookCode()!==''){
					sql+=" bookCode='"+meterBooks.getBookCode()+"',";
				}
				if(undefined!==meterBooks.getBookName()&&meterBooks.getBookName()!==''){
					sql+=" bookName='"+meterBooks.getBookName()+"',";
				}
				if(undefined!==meterBooks.getAnchor()&&meterBooks.getAnchor()!==''){
					sql+=" anchor='"+meterBooks.getAnchor()+"',";
				}
				if(undefined!==meterBooks.getStatus()&&meterBooks.getStatus()!==''){
					sql+=" status='"+meterBooks.getStatus()+"',";
				}
				if(undefined!==meterBooks.getDel()&&meterBooks.getDel()!==''){
					sql+=" del='"+meterBooks.getDel()+"',";
				}
				sql=sql.substring(0,sql.length-1)+' WHERE bookId=?'
                db.executeSql(sql,[meterBooks.getBookId()],
                    ()=> {
                        this._successCB('updateMeterBooks');
                        resolve();
                    },
                    (err)=> {
                        this._errorCB('updateMeterBooks', err);
                        reject(err);
                    });
            }else{
                reject('db not open');
            }
        });
    }
	
	findAllMeterBooks(){//获取记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+MeterBooks_TABLE_NAME+' WHERE del!=-1',[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('findMeterBooks');
                    },(err)=>{
                        reject(err);
                        this._errorCB('findMeterBooks',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	findMeterBooks(isDownload){//获取记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql("SELECT m.bookId,m.bookCode,m.bookName FROM "+MeterBooks_TABLE_NAME+" m WHERE m.isDownload=? ",[isDownload],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('findMeterBooks');
                    },(err)=>{
                        reject(err);
                        this._errorCB('findMeterBooks',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	findMeterBooksByMaxAnchor(){//获取客户端最近一次同步的时间
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT MAX(anchor)anchor FROM '+MeterBooks_TABLE_NAME+' WHERE del!=-1 LIMIT 1',[],
                    (results)=>{
                        if(results.rows.length > 0){
                            resolve(results.rows.item(0));
                        }else {
                            reject('not find item');
                        }

                        this._successCB('findMeterBooksByMaxAnchor')
                    },(err)=>{
                        reject(err);
                        this._errorCB('findMeterBooksByMaxAnchor',err)
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	findMeterBooksById(bookId){//获取id查询记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+MeterBooks_TABLE_NAME +' WHERE bookId=?',[bookId],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('findMeterBooksById');
                    },(err)=>{
                        reject(err);
                        this._errorCB('findMeterBooksById',err)
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	deleteMeterBooks(bookId){//删除
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('DELETE FROM '+MeterBooks_TABLE_NAME+' WHERE bookId=?',[bookId],
                    ()=>{
                        resolve();
                        this._successCB('deleteMeterBooks');
                    },(err)=>{
                        reject(err);
                        this._errorCB('deleteMeterBooks',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	dropTable(){    
		db.transaction((tx)=>{      
			tx.executeSql('drop table '+MeterBooks_TABLE_NAME,[],
				()=>{       });    
			},(err)=>{      
				this._errorCB('transaction', err);    
			},()=>{      
				this._successCB('transaction');    
		}); 
		
		db.transaction((tx)=>{      
			tx.executeSql('drop table '+CostMaster_TABLE_NAME,[],
				()=>{       });    
			},(err)=>{      
				this._errorCB('transaction', err);    
			},()=>{      
				this._successCB('transaction');    
		});
		
		db.transaction((tx)=>{      
			tx.executeSql('drop table '+History_TABLE_NAME,[],
				()=>{       });    
			},(err)=>{      
				this._errorCB('transaction', err);    
			},()=>{      
				this._successCB('transaction');    
		}); 
	}
	
	deleteCostMasterByBookId(currMonth,meterCode){//删除记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('DELETE FROM '+CostMaster_TABLE_NAME +' WHERE currMonth=? AND meterCode=?',[currMonth,meterCode],
                    ()=>{
                        resolve();
                        this._successCB('deleteCostMasterByBookId');
                    },(err)=>{
                        reject(err);
                        this._errorCB('deleteCostMasterByBookId',err);
                    });
            }else {
                reject('db not open');
            }
        });

    }
	
    findCostMasterByBookId(costMaster){//获取cost_master记录
        return new Promise((resolve, reject)=>{
            if(db){
				let sql=" SELECT * FROM "+CostMaster_TABLE_NAME +" WHERE bookId=? ";
				if(undefined!==costMaster.getQuantity()&&costMaster.getQuantity()==0){
					sql+=" AND quantity<>0 AND quantity IS NOT NULL ";
				}
				if(undefined!==costMaster.getCurrReadingTime()&&costMaster.getCurrReadingTime()!==null){
					sql+=" AND curr_reading_time<>'null' AND curr_reading_time IS NOT NULL ";
				}
				sql=sql.substring(0,sql.length-1);
                db.executeSql(sql,[costMaster.getBookId()],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('findCostMasterByBookId');
                    },(err)=>{
                        reject(err);
                        this._errorCB('findCostMasterByBookId',err)
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
	findCostMasterBySno(sno){//获取cost_master记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+CostMaster_TABLE_NAME +' WHERE sno=? LIMIT 1',[sno],
                    (results)=>{
                        if(results.rows.length > 0){
                            resolve(results.rows.item(0));
                        }else {
                            reject('not find item');
                        }

                        this._successCB('findCostMasterBySno')
                    },(err)=>{
                        reject(err);
                        this._errorCB('findCostMasterBySno',err)
                    });
            }else {
                reject('db not open');
            }
        });

    }
	
    listCostMaster(pageSize,index){//获取cost_master记录列表
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+CostMaster_TABLE_NAME +' LIMIT '+pageSize+' OFFSET '+((index-1)*pageSize),[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listCostMaster');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listCostMaster',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
    updateCostMaster(costMaster){//更新记录
        return new Promise((resolve, reject)=>{
            if(db) {
                db.executeSql('UPDATE ' + CostMaster_TABLE_NAME + ' SET curr_reading=?,curr_reading_time=?,quantity=? WHERE sno="' + costMaster.getSno()+'"',
                    [costMaster.getCurrReading(),costMaster.getCurrReadingTime(),costMaster.getQuantity()],
                    ()=> {
                        this._successCB('updateCostMaster');
                        resolve();
                    },
                    (err)=> {
                        this._errorCB('updateCostMaster', err);
                        reject(err);
                    });
            }else{
                reject('db not open');
            }
        });
    }
	
    listHistory(pageSize,index){//获取观看历史记录列表
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+History_TABLE_NAME +' LIMIT '+pageSize+' OFFSET '+((index-1)*pageSize),[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listHistory');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listHistory',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }
	
    close(){
        if(db){
            this._successCB('close');
            db.close();
        }else {
            console.log("SQLiteStorage not open");
        }
        db = null;
    }
	
    _successCB(name){
        console.log("SQLiteStorage "+name+" success");
    }
	
    _errorCB(name, err){
        console.log("SQLiteStorage "+name+" error:"+JSON.stringify(err));
    }
}

